package com.itheima.poi_test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author ：yuan_he_song
 */
public class PoiTest {

    public static void main(String[] args) throws Exception{
        // writeExcelFont();
        // insertPicture();
        readExcel();
    }

    /**
     * 读取Excel 并解析
     *      sheet.getLastRowNum() 最后一行的索引
     *      row.getLastCellNum() 最后一个单元格的列号
     */
    private static void readExcel() throws Exception{
        // 根据Excell创建工作簿对象
        Workbook wb = new XSSFWorkbook("D:\\学成在线资料\\员工管理及POI入门\\资源\\Excel相关\\demo.xlsx");
        // 得到第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum); // 获取每一个行对象
            StringBuilder sb = new StringBuilder();
            for (int cellNum = 2; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum); // 获取每一行的单元格对象(每一列)
                Object value = getCellValue(cell);
                /** 处理单元格内容 */
                sb.append(value).append("-");
            }
            System.out.println(sb.toString());
        }
    }

    /** 根据单元格数据类型,获取单元格中的数据 */
    private static Object getCellValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        Object value = null;
        switch (cellType){
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC: // 数字类型: 日期和数字
                if (DateUtil.isCellDateFormatted(cell)){
                    /** 日期格式 */
                    value = cell.getDateCellValue();
                }else {
                    /** 数字格式 */
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: // 公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

    /** 向Excel写入文本内容 */
    private static void writeExcelFont() throws IOException {
        Workbook wb = new XSSFWorkbook(); // 2007 xlsx
        Sheet sheet = wb.createSheet("poi_test");
        Row row = sheet.createRow(2);
        Cell cell = row.createCell(2);
        cell.setCellValue("传智播客");

        // 创建单元格样式, 有sheet才能创建样式
        CellStyle style = wb.createCellStyle();

        // 创建字体
        Font font = wb.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short)28);// 字号
        style.setFont(font);

        // 行高和列宽
        row.setHeightInPoints(50); // 行高
        sheet.setColumnWidth(2,30*256); // 列宽

        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 设置单元格样式
        cell.setCellStyle(style);

        FileOutputStream fos = new FileOutputStream("D:\\setup\\test2.xlsx");
        wb.write(fos);
        fos.close();
    }

    /** 向Excel插入图片 */
    public static void insertPicture() throws Exception{
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet = wb.createSheet("test");

        //读取图片流
        FileInputStream stream = new FileInputStream("D:\\setup\\logo.jpg");
        //转化二进制数组
        byte[] bytes = IOUtils.toByteArray(stream);
        stream.read(bytes);
        //向POI内存中添加一张图片，返回图片在图片集合中的索引
        int index = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);//参数一：图片的二进制数据，参数二：图片类型
        //绘制图片工具类
        CreationHelper helper = wb.getCreationHelper();
        //创建一个绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        //创建锚点，设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setRow1(0);
        anchor.setCol1(0);
        //绘制图片
        Picture picture = patriarch.createPicture(anchor, index);//图片位置，图片的索引
        picture.resize();//自适应渲染图片

        //文件流
        FileOutputStream pis = new FileOutputStream("D:\\setup\\test3.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}
